﻿Imports System
Imports System.Data
Imports System.Data.OleDb

Partial Class EditJobs
    Inherits System.Web.UI.Page

    Protected Sub ddlIndustries_DataBound(ByVal sender As Object, ByVal e As System.EventArgs) Handles ddlIndustries.DataBound
        ddlJobs_SelectedIndexChanged(sender, e)
    End Sub

    Protected Sub ddlIndustries_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles ddlIndustries.SelectedIndexChanged
        'tblIndustrySkillsets.SelectCommand = "SELECT * FROM [tblIndustrySkillsets] WHERE [industryId] = '" & ddlIndustries.SelectedValue & "'"
    End Sub

    'Protected Sub ddlSkillsets_DataBound(ByVal sender As Object, ByVal e As System.EventArgs) Handles ddlSkillsets.DataBound
    '    If txtTitle.Text = "" Then
    '        Exit Sub
    '    End If
    '    For i As Integer = 0 To ddlSkillsets.Items.Count - 1
    '        ddlSkillsets.SelectedIndex = i
    '        If ddlSkillsets.SelectedItem.Text = txtTitle.Text Then
    '            Exit For
    '        End If
    '    Next
    'End Sub

    Protected Sub btnFindJob_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnFindJob.Click
        Dim strIndustry As String = ""
        Dim strTitle As String = ""

        'Assume that access_db/Jobs.mdb exists in the root of hosting directory
        'Dim strDataSource As String = Server.MapPath("../Jobs.mdb")

        'Assume that access_db/Jobs.mdb exists in the current directory
        Dim strDataSource As String = Server.MapPath("access_db/Jobs.mdb")

        'Assume that access_db/Jobs.mdb exists in the parent directory of C: drive
        'Dim strDataSource As String = Server.MapPath("../access_db/Jobs.mdb")

        Dim strConnection As String = "Provider=Microsoft.Jet.OleDb.4.0;" & _
                                        "Data source=" & strDataSource
        Dim objConnection As New OleDbConnection(strConnection)
        Dim selectSQL As String = "SELECT * FROM tblJobs WHERE jobId = " & Val(txtJobId.Text)
        Try
            objConnection.Open()
            Dim objCommand As New OleDbCommand(selectSQL, objConnection)
            Dim objReader As OleDbDataReader
            objReader = objCommand.ExecuteReader(CommandBehavior.CloseConnection)
            If objReader.HasRows Then
                While objReader.Read()
                    lblDate.BackColor = Drawing.Color.Empty
                    lblDate.Text = Trim(objReader.Item("jobDate").ToString)
                    strIndustry = Trim(objReader.Item("jobIndustry"))
                    strTitle = Trim(objReader.Item("jobTitle"))
                    txtSpecTitle.Text = Trim(objReader.Item("jobSpecTitle"))
                    txtTitle.Text = Trim(objReader.Item("jobTitle"))
                    txtCity.Text = Trim(objReader.Item("jobCity"))
                    txtZip.Text = Trim(objReader.Item("jobZip"))
                    txtPayRate.Text = Trim(objReader.Item("jobPayRate"))
                    txtStatus.Text = Trim(objReader.Item("jobStatus"))
                    txtJobDescription.Text = Trim(objReader.Item("jobDescription"))
                End While
                For i As Integer = 1 To 21
                    ddlIndustries.SelectedIndex = i
                    If ddlIndustries.SelectedItem.Text = strIndustry Then
                        ddlIndustries_SelectedIndexChanged(ddlIndustries, e)
                        Exit For
                    End If
                Next i
            Else
                lblDate.BackColor = Drawing.Color.Red
                lblDate.Text = "Record not found!"
                strIndustry = ""
                strTitle = ""
                txtSpecTitle.Text = ""
                txtCity.Text = ""
                txtZip.Text = ""
                txtPayRate.Text = ""
                txtStatus.Text = ""
                txtJobDescription.Text = ""
                txtTitle.Text = ""
                objReader.Close()
                Exit Sub
            End If
            objReader.Close()
            objConnection.Close()
            objConnection.Dispose()
        Catch ex As Exception
            Response.Write("Error: " & ex.Message)
        End Try
    End Sub

    Protected Sub btnCommitChange_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnCommitChange.Click
        Dim strJobDescription As String = txtJobDescription.Text.Replace("'", """")
        txtJobDescription.Text = strJobDescription
        lblDate.Text = Now.Date
        'Assume that access_db/Jobs.mdb exists in the root of hosting directory
        'Dim strDataSource As String = Server.MapPath("../Jobs.mdb")

        'Assume that access_db/Jobs.mdb exists in the current directory
        Dim strDataSource As String = Server.MapPath("db/Jobs.mdb")

        'Assume that access_db/Jobs.mdb exists in the parent directory of C: drive
        'Dim strDataSource As String = Server.MapPath("../access_db/Jobs.mdb")
        Dim strConnection As String = "Provider=Microsoft.Jet.OleDb.4.0;" & _
                                        "Data source=" & strDataSource
        Dim objConnection As New OleDbConnection(strConnection)
        'Dim selectSQL As String = "SELECT jobId FROM tblJobs ORDER BY jobId DESC"
        Dim updateSQL As String = "UPDATE tblJobs SET jobIndustry = '" & ddlIndustries.SelectedItem.Text & _
                                    "', jobSpecTitle = '" & txtSpecTitle.Text & _
                                    "', jobCity = '" & txtCity.Text & "', jobZip = '" & txtZip.Text & _
                                    "', jobDescription = '" & txtJobDescription.Text & _
                                    "', jobPayRate = '" & txtPayRate.Text & _
                                    "', jobDate = '" & lblDate.Text & _
                                    "', jobStatus = '" & txtStatus.Text & "' WHERE jobId = " & Val(txtJobId.Text) & ";"
        Dim intJobId As Integer = 0
        Try
            objConnection.Open()
            Dim objCommand As New OleDbCommand(updateSQL, objConnection)
            objCommand.ExecuteNonQuery()
            txtJobId.Text &= ". Commited change."
            objConnection.Close()
            objConnection.Dispose()
        Catch ex As Exception
            Response.Write("Error: " & ex.Message)
        End Try

    End Sub

    Protected Sub ddlJobs_DataBound(ByVal sender As Object, ByVal e As System.EventArgs) Handles ddlJobs.DataBound
        ddlJobs.SelectedIndex = 0
    End Sub

    Protected Sub ddlJobs_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles ddlJobs.SelectedIndexChanged
        Dim strIndustry As String = ""
        'Dim strTitle As String = ""

        'Assume that access_db/Jobs.mdb exists in the root of hosting directory
        'Dim strDataSource As String = Server.MapPath("../Jobs.mdb")

        'Assume that access_db/Jobs.mdb exists in the current directory
        Dim strDataSource As String = Server.MapPath("access_db/Jobs.mdb")

        'Assume that access_db/Jobs.mdb exists in the parent directory of C: drive
        'Dim strDataSource As String = Server.MapPath("../access_db/Jobs.mdb")

        Dim strConnection As String = "Provider=Microsoft.Jet.OleDb.4.0;" & _
                                        "Data source=" & strDataSource
        Dim objConnection As New OleDbConnection(strConnection)
        Dim iJobId As Integer = Val(ddlJobs.SelectedValue)

        'Dim selectSQL As String = "SELECT * FROM tblJobs WHERE jobId = " & Val(txtJobId.Text)
        Dim selectSQL As String = "SELECT * FROM tblJobs WHERE jobId = " & iJobId
        Try
            objConnection.Open()
            Dim objCommand As New OleDbCommand(selectSQL, objConnection)
            Dim objReader As OleDbDataReader
            objReader = objCommand.ExecuteReader(CommandBehavior.CloseConnection)
            If objReader.HasRows Then
                While objReader.Read()
                    lblDate.BackColor = Drawing.Color.Empty
                    lblDate.Text = Trim(objReader.Item("jobDate").ToString)
                    strIndustry = Trim(objReader.Item("jobIndustry"))
                    'strTitle = Trim(objReader.Item("jobTitle"))
                    txtJobId.Text = (objReader.Item("jobId"))
                    txtSpecTitle.Text = Trim(objReader.Item("jobSpecTitle"))
                    'txtTitle.Text = Trim(objReader.Item("jobTitle"))
                    txtCity.Text = Trim(objReader.Item("jobCity"))
                    txtZip.Text = Trim(objReader.Item("jobZip"))
                    txtPayRate.Text = Trim(objReader.Item("jobPayRate"))
                    txtStatus.Text = Trim(objReader.Item("jobStatus"))
                    txtJobDescription.Text = Trim(objReader.Item("jobDescription"))
                End While
                For i As Integer = 1 To 21
                    ddlIndustries.SelectedIndex = i
                    If Trim(ddlIndustries.SelectedItem.Text) = Trim(strIndustry) Then
                        'ddlIndustries_SelectedIndexChanged(ddlIndustries, e)
                        Exit For
                    End If
                Next i
            Else
                lblDate.BackColor = Drawing.Color.Red
                lblDate.Text = "Record not found!"
                strIndustry = ""
                'strTitle = ""
                txtSpecTitle.Text = ""
                txtCity.Text = ""
                txtZip.Text = ""
                txtPayRate.Text = ""
                txtStatus.Text = ""
                txtJobDescription.Text = ""
                'txtTitle.Text = ""
                objReader.Close()
                Exit Sub
            End If
            objReader.Close()
            objConnection.Close()
            objConnection.Dispose()
        Catch ex As Exception
            Response.Write("Error: " & ex.Message)
        End Try
    End Sub

End Class